﻿using Shop.Common;
using Shop.Implementation.SO;
using Shop.Interface.Customer;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;

namespace Shop.Implementation.Customer
{
    public class ICoupon_V1 : ICoupon
    {
        private static bool CheckToken(string _token, out int idcus)
        {
            string cmdsql = "select IDCus  from CustomerLoginProcess (nolock) where cToken=@cToken ";
            DataTable dtval = HandleSQL.GetData(cmdsql, null, new SqlParameter[]
            {
                new SqlParameter("@cToken",_token)
            });
            if (dtval.Rows.Count == 0)
            {
                idcus = 0;
                return false;
            }
            else
            {
                idcus = (dtval.Rows[0][0].ToString()).GetVal_Int();
                return true;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="customerCouponID"></param>
        /// <param name="orderID"></param>
        /// <param name="IsUse"> true 使用代金券， false 不适用代金券 仅仅计算</param>
        /// <returns></returns>
        #region 获取优惠金额，并使用/不使用
        public static double GetCouponsAmount(SqlCommand cncmd, string customerCouponID, int orderID, double total, bool IsUse)
        {
            string cmdsql = string.Empty;
            string temp = string.Empty;
            if (!string.IsNullOrEmpty(customerCouponID))
            {
                cmdsql = @"
  SELECT   SUM( c.Amount)
 FROM[ShopWPMC].[dbo].[CouponCustomer]
        AS CC
  LEFT JOIN COUPON AS c 
  ON   cc.couponid=c.couponid
  WHERE CustomerCouponID in (
";
                foreach (var i in customerCouponID.Split(','))
                {
                    temp += i + ",";
                }
                temp = temp.Substring(0, temp.Length - 1);
                temp += ")";
                cmdsql += temp;



                DataTable coupon = new DataTable();
                coupon = HandleSQL.GetData(cmdsql, cncmd);
                double amount = string.IsNullOrEmpty(coupon.Rows[0][0].ToString()) ? 0 : double.Parse(coupon.Rows[0][0].ToString());

                #region 新人券不在限制范围内
                cmdsql = @"
SELECT sum(c.Amount)
  FROM  Coupon  as c
  left join  CouponCustomer as cc
  on c.CouponID = cc.CouponID
  where  cc.CustomerCouponID in(" + temp + "  and c.Name='新人券'";
                var tempRead = HandleSQL.GetData(cmdsql, cncmd);
                var tempAmount = string.IsNullOrEmpty(tempRead.Rows[0][0].ToString()) ? 0 : double.Parse(tempRead.Rows[0][0].ToString());
                #endregion
                if (total * 0.05 < amount - tempAmount)
                {
                    throw new Exception("优惠券金额超出了可以使用的范围.");
                }
                if (IsUse) //正式使用优惠券的时候更新优惠券信息
                {
                    cmdsql = @"
update ShopWPMC.dbo.CouponCustomer set OrderID =@OrderID ,UseDate= Getdate()
where CustomerCouponID in(
" + temp;
                    HandleSQL.GetData(cmdsql, null, new SqlParameter[] {
                     new SqlParameter("@OrderID", orderID)
                });

                }
                return amount;
            }
            return 0;
        }
        #endregion


        #region 总额百分5可以用优惠券列表
        public static DataTable GetReceivedCoupons(SqlCommand cncmd, double total, int idcus)
        {
            //新人券（注册时候送的优惠券）是任何时候都可以使用没有5%限制
            string cmdSQL = @"
SELECT  [CustomerCouponID]
      ,cc.[CouponID]
      ,CONVERT(varchar(100),cc.CreateDate, 23)  as CreateDate
      ,CONVERT(varchar(100),cc.StartDate, 23)  as StartDate
      , CONVERT(varchar(100),cc.EndDate, 23)  as EndDate
      ,ca.Name AS ActivityName
      ,c.Amount
      ,c.Name as CouponName
  FROM [ShopWPMC].[dbo].[CouponCustomer] as cc
  left join  CouponActivity as ca
  on cc.ActivityID= ca.ActivityID
  left join  Coupon as c
  on cc.CouponID=c.CouponID
where cc.CustomerID =@CustomerID and (@Total*0.05>=c.Amount or c.Name='新人券' ) and  DATEADD(d,1,cc.EndDate)>GETDATE() and cc.StartDate <=GETDATE()  and OrderID is null
";
            return HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@CustomerID", idcus), new SqlParameter("@Total", total) });
        }

        #endregion


        #region 领优惠券规则  返回可以领取的优惠券
        #region 结算类型
        //结算类型
        public static List<ReceiveCoupon> GetCouponList(SqlCommand cncmd, string _tbdetail, int orderID, int idcus)
        {
            string cmdSQL = string.Empty;
            cmdSQL = @"select districtID from Customer where id=@idcus";
            var district = HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@idcus", idcus) });
            int districtID = (district.Rows[0][0]).GetVal_Int();
            string districtIDs = GetParentsID(cncmd, districtID);
            if (districtIDs.Length == 0) { districtIDs = "1"; }
            cmdSQL = @"
SELECT  ActivityRuleID 
      , car.ActivityID 
      , cInvCode 
      , cInvCodeSub 
      , cInvCCode 
      , car.Amount as AcitivityAmount
      ,ca.CouponID
      ,ca.DistirctID
      ,ca.Name as ActivityName
      ,ca.ActivityCode
  FROM  CouponActivityRule  as car
  left join CouponActivity as ca
  on car.ActivityID =ca.ActivityID
  where ca.PageType = 3 and  ca.EndDate >=GETDATE() and  ca.StartDate <=GETDATE() and ca.DistirctID in(" + districtIDs + @") and type in(2,3)";//pagetype =3 获取结算的时候的领取优惠券规则 type in(2,3) 单品，单品类赠送
            DataTable activitySingle = HandleSQL.GetData(cmdSQL, cncmd);
            cmdSQL = @"
SELECT  ActivityRuleID 
      , car.ActivityID 
      , cInvCode 
      , cInvCodeSub 
      , cInvCCode 
      , car.Amount as AcitivityAmount
      ,ca.CouponID
      ,ca.DistirctID
      ,ca.Name as ActivityName
      ,ca.ActivityCode
  FROM  CouponActivityRule  as car
  left join CouponActivity as ca
  on car.ActivityID =ca.ActivityID
  where ca.PageType = 3  and  ca.EndDate >=GETDATE() and  ca.StartDate <=GETDATE()  and ca.DistirctID  in(" + districtIDs + @")  and type = 4
";//pagetype =3 获取结算的时候的领取优惠券规则 type=4 总额满赠
            DataTable activitySum = HandleSQL.GetData(cmdSQL, cncmd);

            cmdSQL = string.Format(@"
select sdp.cInvCode,sdp.cInvCodeSub,InS.cInvCCode,iSum,iNum from {0} AS sdp
left join  InventorySale as InS
on sdp.cInvCode=InS.cInvCode and sdp.cInvCodeSub = InS.cInvCodeSub
where ID = @orderID
", _tbdetail);
            DataTable detailList = HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@orderID", orderID) });
            List<ReceiveCoupon> list = new List<ReceiveCoupon>();
            double totalAmount = 0;
            for (int i = 0; i < detailList.Rows.Count; i++)
            {
                totalAmount += double.Parse(string.IsNullOrEmpty(detailList.Rows[i][3].ToString()) ? "0" : detailList.Rows[i][3].ToString());
                for (int j = 0; j < activitySingle.Rows.Count; j++)
                {
                    int activityID = (activitySingle.Rows[j]["ActivityID"]).GetVal_Int();
                    int couponID = (activitySingle.Rows[j]["CouponID"]).GetVal_Int();
                    //单品赠送
                    if (detailList.Rows[i][0].ToString() == activitySingle.Rows[j][2].ToString()
                        && detailList.Rows[i][1].ToString() == activitySingle.Rows[j][3].ToString()
                        && !string.IsNullOrEmpty(activitySingle.Rows[j][2].ToString())
                        && !string.IsNullOrEmpty(activitySingle.Rows[j][3].ToString()))
                    {
                        for (int temp = 0; temp < (int)double.Parse(detailList.Rows[i][4].ToString()); temp++)
                            list = AddReceiveCoupon(cncmd, list, activityID, couponID);
                    }
                    //单品类赠送
                    if (detailList.Rows[i][2].ToString() == activitySingle.Rows[j][4].ToString() && !string.IsNullOrEmpty(activitySingle.Rows[j][4].ToString()))
                    {
                        for (int temp = 0; temp < (int)double.Parse(detailList.Rows[i][4].ToString()); temp++)
                            list = AddReceiveCoupon(cncmd, list, activityID, couponID);
                    }

                }
                //满赠送
                for (int j = 0; j < activitySum.Rows.Count; j++)
                {
                    int activityID = (activitySum.Rows[j]["ActivityID"]).GetVal_Int();
                    int couponID = (activitySum.Rows[j]["CouponID"]).GetVal_Int();
                    if (totalAmount >= double.Parse(string.IsNullOrEmpty(activitySum.Rows[j]["AcitivityAmount"].ToString()) ? "0" : activitySum.Rows[j]["AcitivityAmount"].ToString()))
                        list = AddReceiveCoupon(cncmd, list, activityID, couponID);
                }

            }

            return list;
        }


        public static List<ReceiveCoupon> GetCouponList_New(SqlCommand cncmd, string tbmain, string _tbdetail, int orderID, int idcus)
        {
            List<ReceiveCoupon> list = new List<ReceiveCoupon>();
            string cmdSQL = string.Empty;
            cmdSQL = @"select districtID from Customer where id=@idcus";
            var district = HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@idcus", idcus) });
            int districtID = (district.Rows[0][0]).GetVal_Int();
            string districtIDs = GetParentsID(cncmd, districtID);
            #region 获取有效的活动 1、有效期内2、PageType = 3结算类型3、区域内
            cmdSQL = @"
SELECT  [ActivityID] 
FROM [ShopWPMC].[dbo].[CouponActivity]
where  PageType = 3 and    DATEADD(d,1,EndDate) > GETDATE() and  StartDate <=GETDATE() and DistirctID in(" + districtIDs + @") ";//pagetype =3 获取结算的时候的领取优惠券规则  
            DataTable activityLists = HandleSQL.GetData(cmdSQL, cncmd);
            #endregion
            for (int i = 0; i < activityLists.Rows.Count; i++)
            {

                #region 获取每个活动的子规则
                cmdSQL = @"
  select ca.ActivityID,
  ca.Name,
  ca.ActivityCode,
  cad.CouponActivityDetailID,
  cad.CouponID,
  cad.CouponCount,
  cad.ActivityCondition 
  from CouponActivityDetail as cad
  left join CouponActivity as ca
  on cad.ActivityID = ca.ActivityID
  where ca.PageType = 3 and  ca.EndDate >=GETDATE() and  ca.StartDate <=GETDATE() and ca.ActivityID=@ActivityID and ca.DistirctID in(" + districtIDs + @") ";//pagetype =3 获取结算的时候的领取优惠券规则   
                DataTable activityDetails = HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@ActivityID", activityLists.Rows[i][0].GetVal_Int()) });
                #endregion
                for (int j = 0; j < activityDetails.Rows.Count; j++)
                {
                    #region 遍历子活动规则，符合则加入领取优惠券列表中,加入后跳出遍历
                    if (IsMeetTheCondition(cncmd, tbmain, _tbdetail, orderID, idcus, activityDetails.Rows[j][6].GetVal_String()))//传入商品详情表，订单编号，活动条件返回是否对应
                    {
                        //获取优惠券数量
                        int Count = GetCouponCount(cncmd, tbmain, _tbdetail, orderID, idcus, activityDetails.Rows[j][5].GetVal_String());

                        for (var tempCount = 0; tempCount < Count; tempCount++)
                            list = AddReceiveCoupon(cncmd, list, activityDetails.Rows[j][0].GetVal_Int(), activityDetails.Rows[j][4].GetVal_Int());
                        break;
                    }
                    #endregion

                }

            }
            return list;
        }
        /// <summary>
        /// 订单是否符合活动条件   优惠券的是在写订单之后  订单数量（次数）表达式用=号， 买赠在写订单之前 订单数量（次数）表达式用<号
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="_tbdetail">表名称</param>
        /// <param name="orderID">订单ID</param>
        /// <param name="condition">公式化的条件，例如@金额('800123-12')>200 and @数量('800123-t0','800124-11')>1</param>
        /// <returns></returns>
        public static bool IsMeetTheCondition(SqlCommand cncmd, string tbmain, string _tbdetail, int orderID, int idCus, string condition, int IDPromote = 0/*优惠活动的详情ID*/)
        {
            #region 屏蔽没用
            ////condition = "@金额('800123-12312','800124','800125')>200 and @数量('800123','800124',800125')>1";
            //int iGSS = 0;
            //int iGSE = 0;
            //int iTLen = condition.Length;
            //string rval = condition;
            //for (int i = 0; i < condition.Length; i++)
            //{
            //    if (i > condition.Length - 5) { break; }
            //    if (condition.Substring(i, "@金额".Length) == "@金额")
            //    {
            //        iGSS = condition.IndexOf("(", i);
            //        iGSE = condition.IndexOf(")", i);
            //        string s1 = condition.Substring(iGSS + 1, iGSE - iGSS - 1);
            //        double isum = GetSum(cncmd, "金额", _tbdetail, GetProductsSQL(s1), orderID);
            //        //float isum = countiSum(s1);
            //        string ss = condition.Substring(i, iGSE - i + 1);
            //        rval = rval.Replace(ss, isum.ToString());
            //    }
            //    if (condition.Substring(i, "@数量".Length) == "@数量")
            //    {
            //        iGSS = condition.IndexOf("(", i);
            //        iGSE = condition.IndexOf(")", i);
            //        string s1 = condition.Substring(iGSS + 1, iGSE - iGSS - 1);
            //        double isum = GetSum(cncmd, "数量", _tbdetail, GetProductsSQL(s1), orderID);
            //        //float isum = countiNum(condition.Substring(iGSS, iGSE - iGSS - 1));
            //        string ss = condition.Substring(i, iGSE - i + 1);
            //        rval = rval.Replace(ss, isum.ToString());
            //    }
            //    if (condition.Substring(i, "@订单总额".Length) == "@数量")
            //    {
            //        iGSS = condition.IndexOf("(", i);
            //        iGSE = condition.IndexOf(")", i);
            //        string s1 = condition.Substring(iGSS + 1, iGSE - iGSS - 1);
            //        double isum = GetSum(cncmd, "订单总额", _tbdetail, GetProductsSQL(s1), orderID);
            //        //float isum = countiNum(condition.Substring(iGSS, iGSE - iGSS - 1));
            //        string ss = condition.Substring(i, iGSE - i + 1);
            //        rval = rval.Replace(ss, isum.ToString());
            //    }
            //} 
            #endregion
            //套公式，是否符合条件
            string cmdSql = @"select  case when " + SoCount.SplitCondition(condition, cncmd, orderID, tbmain, _tbdetail, idCus, IDPromote) + "  then 1  else 0 end as temp";
            var result = HandleSQL.GetDataObj(cmdSql, cncmd).GetVal_Int();
            return result == 1 ? true : false;
        }
        /// <summary>
        ///    根据送优惠券的数量公式   计算送优惠券数量
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="_tbdetail"></param>
        /// <param name="orderID"></param>
        /// <param name="couponCountStr"></param>
        /// <returns></returns>
        public static int GetCouponCount(SqlCommand cncmd, string tbmain, string _tbdetail, int orderID, int idCus, string couponCountStr, int IDPromote = 0/*优惠活动的详情ID*/)
        {
            //套公式，是否符合条件
            string cmdSql = @"select  " + SoCount.SplitCondition(couponCountStr, cncmd, orderID, tbmain, _tbdetail, idCus, IDPromote) + " ";
            var result = HandleSQL.GetDataObj(cmdSql, cncmd).GetVal_Int();
            return result;
        }
        /// <summary>
        /// 获取具体的商品集sql语句
        /// </summary>
        /// <param name="str"> 例如 "'800123-12','800124-123','800125-sdf'";</param>
        /// <returns></returns>
        private static string GetProductsSQL(string str)
        {
            string sql = string.Empty;
            foreach (var item in str.Split(','))
            {
                sql += " (cInvCode= " + item.Replace("-", "' and  cInvCodeSub= '") + ") ";
                sql += " or ";
            }
            sql = string.IsNullOrEmpty(sql) ? string.Empty : sql.Substring(0, sql.Length - " or ".Length);
            return sql;
        }
        /// <summary>
        /// 计算和
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="type">求和类型：金额、数量、订单总额</param>
        /// <param name="_tbdetail">详情表名称</param>
        /// <param name="products">sql化后的商品集合语句</param>
        /// <param name="orderID">订单ID</param>
        /// <returns></returns>
        private static double GetSum(SqlCommand cncmd, string type, string _tbdetail, string products, int orderID)
        {
            string cmdSql = string.Empty;
            if (string.IsNullOrEmpty(products)) products = " 1=1 ";
            switch (type)
            {
                case "金额":
                    cmdSql = @"select sum(iSum) from " + _tbdetail + " where ID=@OrderID and " + products;
                    break;
                case "数量":
                    cmdSql = @"select sum(iNum) from " + _tbdetail + " where  ID=@OrderID and " + products;
                    break;
                case "订单总额":
                    cmdSql = @"select sum(iSum) from " + _tbdetail + " where  ID=@OrderID and " + products;
                    break;
                case "下单次数":
                    cmdSql = @"select count(*) from " + _tbdetail + " where  ID=@OrderID and " + products;
                    break;
                default:
                    throw new Exception("活动公式有误!");

            }
            return HandleSQL.GetData(cmdSql, cncmd, new SqlParameter[] { new SqlParameter("@OrderID", orderID) }).Rows[0][0].GetVal_Double();
        }



        #region 获取区域id
        private static string GetParentsID(SqlCommand cncmd, int id)
        {
            string ids = string.Empty;

            while (id != 0)  //最顶级的区域id为0  则跳出
            {
                ids += id.ToString();
                id = GetParentID(cncmd, id);
                if (id != 0) ids += ",";
            }
            if (ids.Length == 0) { ids = "1"; }
            return ids;
        }
        private static int GetParentID(SqlCommand cncmd, int id)
        {
            string cmdSQL = string.Empty;

            cmdSQL = @"select parentID from district where ID=@ID";
            var district = HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@ID", id) });
            return district.Rows[0][0].GetVal_Int();
        }
        #endregion

        private static List<ReceiveCoupon> AddReceiveCoupon(SqlCommand cncmd, List<ReceiveCoupon> list, int activityID, int couponID)
        {
            //var temp = list.FirstOrDefault(s => s.ActivityID == activityID && s.CouponID == couponID);
            //if (temp == null)
            //{
            DateTime endDate = DateTime.Now;
            DateTime startDate = DateTime.Parse(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
            string cmdSQL = @"select DayRange,MonthRange,EndDate,Name,Amount from Coupon where CouponID=@CouponID ";
            var tempTB = HandleSQL.GetData(cmdSQL, cncmd, new SqlParameter[] { new SqlParameter("@CouponID", couponID) });
            if (!string.IsNullOrEmpty(tempTB.Rows[0][0].GetVal_String())) endDate = DateTime.Parse(DateTime.Now.AddDays(1 + (tempTB.Rows[0][0]).GetVal_Int()).ToString("yyyy-MM-dd"));
            if (!string.IsNullOrEmpty(tempTB.Rows[0][1].GetVal_String())) endDate = DateTime.Parse(DateTime.Now.AddMonths((tempTB.Rows[0][1]).GetVal_Int()).AddDays(1).ToString("yyyy-MM-dd"));
            if (!string.IsNullOrEmpty(tempTB.Rows[0]["EndDate"].GetVal_String())) endDate = DateTime.Parse(tempTB.Rows[0][2].ToString());

            list.Add(new ReceiveCoupon()
            {
                ActivityName = GetActivityName(cncmd, activityID),
                ActivityID = activityID,
                Count = 1,
                CouponID = couponID,
                Amount = Decimal.Parse(tempTB.Rows[0]["Amount"].ToString()),
                CouponName = tempTB.Rows[0]["Name"].ToString(),
                StartDate = startDate.ToString("yyyy-MM-dd"),
                EndtDate = endDate.ToString("yyyy-MM-dd")
            });
            //}
            //else
            //    temp.Count++;
            return list;
        }
        #endregion

        #region 1注册类型、2轮播图
        //1注册类型、2轮播图
        public static List<ReceiveCoupon> GetCouponList(int idcus, int pageType = 1)
        {
            string cmdSql = string.Empty;
            List<ReceiveCoupon> list = new List<ReceiveCoupon>();
            cmdSql = @"select districtID from Customer where id=@idcus";
            var district = HandleSQL.GetData(cmdSql, null, new SqlParameter[] { new SqlParameter("@idcus", idcus) });
            int districtID = (district.Rows[0][0]).GetVal_Int();
            string districtIDs = GetParentsID(null, districtID);

            cmdSql = @"
 select  DayRange,MonthRange,c.EndDate,c.Name,Amount,ca.ActivityID,c.CouponID,ca.Name as ActivityName,c.CouponCount  from CouponActivity as ca
  left join Coupon  as c
  on ca.CouponID = c.CouponID
  where GETDATE()<= ca.EndDate and GETDATE()>=ca.StartDate
  and ca.PageType = @PageType and ca.DistirctID in ("
        + districtIDs +
        ")";
            var temp = HandleSQL.GetData(cmdSql, null, new SqlParameter[] {
                new SqlParameter("@PageType", pageType)
            });

            for (int j = 0; j < temp.Rows.Count; j++)
            {
                DateTime endDate = DateTime.Now;
                DateTime startDate = DateTime.Parse(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
                if (!string.IsNullOrEmpty(temp.Rows[0][0].GetVal_String())) endDate = DateTime.Parse(DateTime.Now.AddDays(1 + (temp.Rows[0][0]).GetVal_Int()).ToString("yyyy-MM-dd"));
                if (!string.IsNullOrEmpty(temp.Rows[0][1].GetVal_String())) endDate = DateTime.Parse(DateTime.Now.AddMonths((temp.Rows[0][1].ToString()).GetVal_Int()).AddDays(1).ToString("yyyy-MM-dd"));
                if (!string.IsNullOrEmpty(temp.Rows[0][2].GetVal_String())) endDate = DateTime.Parse(temp.Rows[0][2].ToString());

                for (int count = 0; count < temp.Rows[0][8].GetVal_Int(); count++)
                    list.Add(new ReceiveCoupon()
                    {
                        ActivityName = temp.Rows[0][7].GetVal_String(),
                        ActivityID = temp.Rows[0][5].GetVal_Int(),
                        Count = 1,
                        CouponID = temp.Rows[0][6].GetVal_Int(),
                        Amount = temp.Rows[0][4].GetVal_Decimal(),
                        CouponName = temp.Rows[0][3].GetVal_String(),
                        StartDate = startDate.ToString("yyyy-MM-dd"),
                        EndtDate = endDate.ToString("yyyy-MM-dd")
                    });
            }

            return list;
        }


        #endregion

        #endregion
        #region 个人代金券库


        /// <summary>
        /// 
        /// </summary>
        /// <param name="idcus"></param>
        /// <param name="type">0有效可用，1已使用，2已经失效</param>
        /// <returns></returns>
        public override Dictionary<string, object> GetUserCoupon(int idcus, int type = 0)
        {
            string cmdsql = @"
        SELECT CONVERT(varchar(100), cc.StartDate, 23) as StartDate,  cc.CustomerCouponID,CONVERT(varchar(100), cc.CreateDate, 23) as CreateDate,CONVERT(varchar(100), cc.EndDate, 23) as EndDate,cc.UseDate,cc.OrderID,c.Name as CouponName,c.Amount, ca.ActivityCode,ca.Name as ActivityName,case when GETDATE()> cc.EndDate THEN 'true'  else 'false' end as IsExpire
          FROM ShopWPMC.dbo.CouponCustomer as cc
          left join ShopWPMC.dbo.Coupon as c on c.CouponID =cc.CouponID
           left join ShopWPMC.dbo.CouponActivity as ca
          on ca.ActivityID =cc.ActivityID
          where cc.CustomerID = @CustomerID
        ";
            switch (type)
            {
                case 0:
                    cmdsql += " and OrderID is  null and CONVERT(varchar(100), GETDATE(), 23)<= cc.EndDate ";
                    break;
                case 1:
                    cmdsql += " and OrderID is not null ";
                    break;
                case 2:
                    cmdsql += " and OrderID is null and CONVERT(varchar(100), GETDATE(), 23)> cc.EndDate ";
                    break;
                default: throw new Exception("没有该类型代金券.");
            }
            DataTable temp = HandleSQL.GetData(cmdsql, null, new SqlParameter[] { new SqlParameter("@CustomerID", idcus) });

            return ReturnValue.Get("成功", temp.ToList(), true);

        }

        public override Dictionary<string, object> GetUserTotalCoupon(int idcus)
        {
            double Total;
            double Count;
            double Amount;
            string cmdsql = @"
        SELECT SUM( Amount)
        FROM ShopWPMC.dbo.CouponCustomer as cc
          left join ShopWPMC.dbo.Coupon as c on c.CouponID =cc.CouponID
           left join ShopWPMC.dbo.CouponActivity as ca
          on ca.ActivityID =cc.ActivityID
          where cc.CustomerID = @CustomerID
        and OrderID is  null and GETDATE()<= cc.EndDate 
        ";

            var temp = HandleSQL.GetData(cmdsql, null, new SqlParameter[] { new SqlParameter("@CustomerID", idcus) });
            Total = string.IsNullOrEmpty(temp.Rows[0][0].ToString()) ? 0 : double.Parse(temp.Rows[0][0].ToString());
            cmdsql = @"
        SELECT COUNT(*) as count,SUM(Amount) as amount
        FROM ShopWPMC.dbo.CouponCustomer as cc
          left join ShopWPMC.dbo.Coupon as c on c.CouponID =cc.CouponID
           left join ShopWPMC.dbo.CouponActivity as ca
          on ca.ActivityID =cc.ActivityID
          where OrderID is  null and   DATEADD(d,3,GETDATE())>= cc.EndDate 
           and GETDATE()<= cc.EndDate  
           and  cc.CustomerID = @CustomerID
        ";
            temp = HandleSQL.GetData(cmdsql, null, new SqlParameter[] { new SqlParameter("@CustomerID", idcus) });
            Count = string.IsNullOrEmpty(temp.Rows[0][0].ToString()) ? 0 : double.Parse(temp.Rows[0][0].ToString());
            Amount = string.IsNullOrEmpty(temp.Rows[0][1].ToString()) ? 0 : double.Parse(temp.Rows[0][1].ToString());

            Dictionary<string, object> dfulldata = new Dictionary<string, object>();
            dfulldata.Add("result", 1);
            dfulldata.Add("resultdetail", "数据获取成功");
            dfulldata.Add("Count", Count);
            dfulldata.Add("Total", Total);
            dfulldata.Add("Amount", Amount);
            return dfulldata;
        }
        #endregion
        #region 代金券领取

        public override Dictionary<string, object> ReceivedCoupon(int idcus)
        {
            NameValueCollection nvc = HttpContext.Current.Request.Form;
            int couponID = (nvc["couponID"]).GetVal_Int();
            int activityID = (nvc["activityID"]).GetVal_Int();
            string orderNum = (nvc["orderNum"]).GetVal_String();
            int pageType = string.IsNullOrEmpty((nvc["pageType"]).GetVal_String()) ? 3 : (nvc["pageType"]).GetVal_Int();
            //pageType 不传参数默认为3

            return ReceivedCoupon(idcus, couponID, activityID, orderNum, pageType);
        }

        /// <summary>
        /// 代金券领取
        /// </summary>
        /// <param name="idcus"></param>
        /// <param name="couponID"></param>
        /// <param name="activityID"></param>
        /// <param name="orderNum">pageType有值的时候不能为空</param>
        /// <param name="pageType">1、注册类型2、、轮播图类型3、结算类型 pageType 不传参数默认为3</param>
        /// <returns></returns>
        public static Dictionary<string, object> ReceivedCoupon(int idcus, int couponID, int activityID, string orderNum, int pageType = 3)
        {

            DateTime endDate = DateTime.Now;
            DateTime startDate = DateTime.Parse(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
            DataTable temp;
            int? orderID = null;
            string ActivityName = string.Empty;
            string CouponName = string.Empty;
            double CouponAmount = 0;
            using (SqlConnection cn = new SqlConnection(HandleSQL.cConnstr))
            {
                try
                {
                    SqlCommand cncmd = new SqlCommand();
                    cncmd.Connection = cn;
                    cn.Open();
                    string cmdsql = string.Empty;
                    #region 验证领用是否重复领取代金券
                    switch (pageType)
                    {
                        case 1: //注册页面的代金券领用
                            if (!HasActivity(cncmd, activityID, 1))
                            {
                                cn.Close(); cn.Dispose();
                                return ReturnValue.Get("不存在领取代金券活动", false);
                            }
                            if (HasReceivedCoupon(cncmd, activityID, idcus))
                            {
                                cn.Close(); cn.Dispose();
                                return ReturnValue.Get("不能重复领取该活动代金券", false);
                            }
                            break;
                        case 2://轮播图页面领用
                            if (!HasActivity(cncmd, activityID, 2))
                            {
                                cn.Close(); cn.Dispose();
                                return ReturnValue.Get("不存在领取代金券活动", false);
                            }
                            if (HasReceivedCoupon(cncmd, activityID, idcus))
                            {
                                cn.Close(); cn.Dispose();
                                return ReturnValue.Get("不能重复领取该活动代金券", false);
                            }
                            break;
                        case 3:
                            cmdsql = @"
        select ID from SoPre
        where cSOCode=@orderNum
        ";
                            var orderId = HandleSQL.GetDataObj(cmdsql, cncmd, new SqlParameter[] { new SqlParameter("@orderNum", orderNum) });
                            if (orderId == null)
                            {
                                cn.Close(); cn.Dispose();
                                return ReturnValue.Get("结算订单不存在,未能领取", false);
                            }
                            orderID = orderId.GetVal_Int();
                            break;//结算领用  检查是否有存在结算订单
                        default: return ReturnValue.Get("pageType类型错误", false);

                    }
                    #endregion
                    #region 验证代金券类型是否存在，并且计算过期日期

                    cmdsql = @"
           select DayRange,MonthRange,EndDate,Name,Amount from ShopWPMC.dbo.Coupon 
        where CouponID = @CouponID
        ";
                    temp = HandleSQL.GetData(cmdsql, cncmd, new SqlParameter[] { new SqlParameter("@CouponID", couponID) });
                    if (temp.Rows.Count <= 0)
                    {
                        cn.Close(); cn.Dispose();
                        return ReturnValue.Get("不存在该类型的代金券" + couponID, false);
                    }
                    if (!string.IsNullOrEmpty(temp.Rows[0][0].GetVal_String())) endDate = DateTime.Parse(DateTime.Now.AddDays(1 + (temp.Rows[0][0]).GetVal_Int()).ToString("yyyy-MM-dd"));
                    if (!string.IsNullOrEmpty(temp.Rows[0][1].GetVal_String())) endDate = DateTime.Parse(DateTime.Now.AddMonths((temp.Rows[0][1]).GetVal_Int()).AddDays(1).ToString("yyyy-MM-dd"));
                    if (!string.IsNullOrEmpty(temp.Rows[0][2].GetVal_String())) endDate = DateTime.Parse(temp.Rows[0][2].ToString());
                    CouponName = temp.Rows[0][3].GetVal_String();
                    CouponAmount = temp.Rows[0][4].GetVal_Double();
                    #endregion
                    #region 插入一条代金券信息
                    cmdsql = @"
           insert into ShopWPMC.dbo.CouponCustomer
           (CustomerID, CouponID, ActivityID,CreateDate,StartDate,EndDate,ReceiveOrderID) VALUES
          ( @CustomerID,@CouponID,@ActivityID,GETDATE(),@StartDate,@EndDate," + (orderID == null ? "NULL" : orderID.ToString()) + ")";
                    #region 注册用券当天可用
                    if (pageType == 1)
                    {
                        startDate = startDate.AddDays(-1);
                        endDate = endDate.AddDays(-1);
                    }
                    #endregion
                    temp = HandleSQL.GetData(cmdsql, cncmd, new SqlParameter[] {
                                    new SqlParameter("@CustomerID", idcus),
                                    new SqlParameter("@CouponID", couponID),
                                    new SqlParameter("@ActivityID", activityID),
                                    new SqlParameter("@StartDate", startDate),
                                    new SqlParameter("@EndDate", endDate)
                                });
                    #endregion


                    ActivityName = GetActivityName(cncmd, activityID);
                }
                finally
                { cn.Close(); cn.Dispose(); }
            }


            Dictionary<string, object> dfulldata = new Dictionary<string, object>();
            dfulldata.Add("result", 1);
            dfulldata.Add("startDate", startDate.ToString("yyyy.MM.dd"));
            dfulldata.Add("endDate", endDate.ToString("yyyy.MM.dd"));
            dfulldata.Add("activityName", ActivityName);
            dfulldata.Add("couponName", CouponName);
            dfulldata.Add("couponAmount", CouponAmount);
            return dfulldata;
        }

        /// <summary>
        /// 获取活动名称
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="activityID"></param>
        /// <returns></returns>
        private static string GetActivityName(SqlCommand cncmd, int activityID)
        {
            string cmdsql = @"select  name   from CouponActivity where ActivityID =@ActivityID";

            var temp = HandleSQL.GetData(cmdsql, cncmd, new SqlParameter[] {
                                    new SqlParameter("@ActivityID", activityID)
                                });
            return temp.Rows[0][0].ToString();
        }

        /// <summary>
        /// 是否已经重复领取活动优惠券
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="activityID"></param>
        /// <param name="idcus"></param>
        /// <returns></returns>
        private static bool HasReceivedCoupon(SqlCommand cncmd, int activityID, int idcus)
        {
            string cmdsql = @"
        select count(*) from ShopWPMC.dbo.CouponCustomer 
        where ActivityID=@ActivityID and CustomerID=@CustomerID
        ";
            var temp = HandleSQL.GetData(cmdsql, cncmd, new SqlParameter[] { new SqlParameter("@ActivityID", activityID), new SqlParameter("@CustomerID", idcus) });
            if ((temp.Rows[0][0]).GetVal_Int() > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 是否存在优惠活动（轮播图活动/新注册活动）
        /// </summary>
        /// <param name="cncmd"></param>
        /// <param name="activityID"></param>
        /// <param name="pageType">1注册类型、2轮播图</param>
        /// <returns></returns>
        private static bool HasActivity(SqlCommand cncmd, int activityID, int pageType)
        {
            string cmdsql = @"
        SELECT  COUNT(*)
          FROM [ShopWPMC].[dbo].[CouponActivity]
          where GETDATE()<= EndDate and GETDATE()>=StartDate
          and PageType = @PageType and ActivityID=@ActivityID
        ";
            var temp = HandleSQL.GetData(cmdsql, cncmd, new SqlParameter[] {
                        new SqlParameter("@PageType", pageType),
                        new SqlParameter("@ActivityID", activityID) });
            if ((temp.Rows[0][0]).GetVal_Int() > 0)
            {
                return true;
            }
            return false;
        }

        #endregion
    }


    public class ReceiveCoupon
    {
        public int ActivityID { get; set; }
        public int CouponID { get; set; }
        public int Count { get; set; }
        public string ActivityName { get; set; }
        public string CouponName { get; set; }
        public string StartDate { get; set; }
        public string EndtDate { get; set; }
        public decimal Amount { get; set; }
    }
}